import openpyxl
import re
import os
from tkinter import *
from tkinter import ttk
from tkinter.filedialog import askopenfilename


# 判断变量类型的函数
def typeof(variate):
    type = None
    if isinstance(variate, int):
        type = "int"
    elif isinstance(variate, str):
        type = "str"
    elif isinstance(variate, float):
        type = "float"
    elif isinstance(variate, list):
        type = "list"
    elif isinstance(variate, tuple):
        type = "tuple"
    elif isinstance(variate, dict):
        type = "dict"
    elif isinstance(variate, set):
        type = "set"
    return type


def xlsxReader(completePath, sheetNumber):
    wb = openpyxl.load_workbook(completePath)
    sheet_names = wb.sheetnames
    ws = wb[sheet_names[sheetNumber - 1]]
    allData = []
    for row in ws.rows:
        rowData = []
        for cell in row:
            cellValue = 'Null'
            if cell.value is not None:
                cellValue = cell.value
            rowData.append(cellValue)
        allData.append(rowData)

    return allData


def rejoinString(allData, rejoinColumns, prefix, suffix, seperator, ignoreRows):
    result = ''
    repeatItems = getRepeatItems(allData, rejoinColumns)
    if len(repeatItems) != 0:
        return "存在重复！每一组括号内分别为重复项和重复次数：" + str(repeatItems)

    for index in range(len(allData)):
        if index in ignoreRows:
            continue
        rowData = allData[index]
        for columnNubmer in rejoinColumns:
            if rowData[columnNubmer] == 'Null':
                continue
            result += str(rowData[columnNubmer]) + seperator
    if result[-1] == seperator:
        result = result[0:-1]
    result = prefix + result + suffix
    return result


def getRealIndex(falseIndexs):
    realIndex = []
    for index in falseIndexs:
        realIndex.append(int(index) - 1)
    return realIndex


def transferStringListToNumberList(stringList):
    numberList = []
    for i in stringList:
        numberList.append(int(i))
    return numberList


def judgeGroup(numbers, divisor, groupSize):
    maxNumber = max(transferStringListToNumberList(numbers))
    minNubmer = min(transferStringListToNumberList(numbers))
    minStartBase = minNubmer // divisor * divisor
    maxStartBase = maxNumber // divisor * divisor
    realMin = minNubmer % divisor
    realMax = maxNumber % divisor
    startGroup = (realMin - 1) // groupSize
    endGroup = (realMax - 1) // groupSize
    rangeStart = startGroup * groupSize + 1
    rangeEnd = endGroup * groupSize + groupSize
    return [minStartBase + rangeStart, maxStartBase + rangeEnd]


def repeatCheck(allData, operateColumns):
    repeatItems = getRepeatItems(allData, operateColumns)
    checkResult = ''
    if len(repeatItems) == 0:
        checkResult = '正常无重复'
    else:
        checkResult = '存在重复！每一组括号内分别为重复项和重复次数：' + str(repeatItems)
    return checkResult


def getRepeatItems(allData, operateColumns):
    numberDict = {}
    for index in range(len(allData)):
        if index == 0:
            continue
        rowData = allData[index]
        for columnNubmer in operateColumns:
            if rowData[columnNubmer] == 'Null':
                continue
            if numberDict.get(rowData[columnNubmer]) == None:
                numberDict[rowData[columnNubmer]] = 1
            elif numberDict.get(rowData[columnNubmer]) != None:
                numberDict[rowData[columnNubmer]] = numberDict[rowData[columnNubmer]] + 1
    repeatItems = []
    for i in numberDict:
        if numberDict[i] > 1:
            repeatItems.append([i, numberDict[i]])
    return repeatItems


def judgeAllGroup(allData, judgeColumns, groupLineSize, divisor):
    groups = []
    groupData = []
    allData.pop(0)
    for index in range(len(allData)):
        rowData = allData[index]
        for columnNubmer in judgeColumns:
            if rowData[columnNubmer] == 'Null':
                continue
            groupData.append(rowData[columnNubmer])
        if len(groupData) == 0:
            continue
        if index == len(allData) - 1:
            groups.append(judgeGroup(groupData, divisor, 32))
            continue
        if (index + 1) % groupLineSize == 0:
            groups.append(judgeGroup(groupData, divisor, 32))
            groupData = []
    return groups

root = Tk()
root.title('爱你的工具人 (´･ω･｀)')
pathString = StringVar()
sheetNumber = StringVar()
operationNumber = StringVar()
operationColumn = StringVar()
resultString = StringVar()



def selectPath():
    path_ = askopenfilename()
    pathString.set(path_)


def judgeNumbers(allData, dataColumns):
    groupData = []
    for index in range(len(allData)):
        if index == 0:
            continue
        rowData = allData[index]
        for columnNubmer in dataColumns:
            if rowData[columnNubmer] == 'Null':
                continue
            groupData.append(rowData[columnNubmer])

    notNumberStr = []
    for i in groupData:
        if typeof(i) == 'int':
            continue
        elif typeof(i) == 'str':
            if i.strip().isdigit():
                continue
            else:
                notNumberStr.append(i)
        else:
            notNumberStr.append(i)
    return notNumberStr


def operation():
    operateNumberString = operationNumber.get()[:1]
    rejoinColumns = re.split(',|，', operationColumn.get())
    if pathString.get().endswith(".xlsx") == False:
        resultString.set("亲爱的，很抱歉摸摸哒 (つд`) 当前只支持xlsx格式哦~~~")
        return
    allData = xlsxReader(pathString.get(), int(sheetNumber.get()))

    notNubmerNumbers = judgeNumbers(allData, getRealIndex(rejoinColumns))
    if len(notNubmerNumbers) != 0:
        resultString.set("存在不为数字的项：" + str(notNubmerNumbers))
        return

    if operateNumberString == '1':
        rejoinResult = rejoinString(allData, getRealIndex(rejoinColumns), 'harbor.ahead(', ')', ',', [0])
        resultString.set(rejoinResult)
    if operateNumberString == '2':
        groups = judgeAllGroup(allData, getRealIndex(rejoinColumns), 8, 5000)
        resultString.set(groups)
    if operateNumberString == '3':
        checkResult = repeatCheck(allData, getRealIndex(rejoinColumns))
        resultString.set(checkResult)


if __name__ == '__main__':
    # C:\Users\Administrator\Desktop\印象渠道-《剑玲珑》专服1合区需求-5.29（2合1和4合1都有）-test.xlsx
    pathFrame = Frame(root)
    pathFrame.pack(side=TOP)
    sheetNumberFrame = Frame(root)
    sheetNumberFrame.pack(side=TOP)
    operationCountFrame = Frame(root)
    operationCountFrame.pack(side=TOP)
    operationColumnFrame = Frame(root)
    operationColumnFrame.pack(side=TOP)
    buttonFrame = Frame(root)
    buttonFrame.pack(side=TOP)
    resultFrame = Frame(root)
    resultFrame.pack(side=TOP)

    pathMessage = Entry(pathFrame, textvariable=pathString, width='100')
    sheetMessage = Entry(sheetNumberFrame, textvariable=sheetNumber, width='50')
    operationComboBox = ttk.Combobox(operationCountFrame, width=12, textvariable=operationNumber)
    operationComboBox['values'] = ('1-拼接命令', '2-计算区间', '3-重复检查')
    operationColumnMessage = Entry(operationColumnFrame, textvariable=operationColumn, width='50')
    myButton = Button(buttonFrame, command=operation, width='10', text="开始处理")
    resultMessage = Entry(resultFrame, textvariable=resultString, width='200')

    root.geometry('1280x720')

    pathPrompt = Label(pathFrame, text="请选择xlsx文件的绝对路径：")
    pathPrompt.pack(side=LEFT)
    pathMessage.pack(side=LEFT)
    pathButton = Button(pathFrame, text="路径选择", command=selectPath)
    pathButton.pack(side=LEFT)

    sheetPrompt = Label(sheetNumberFrame, text="请输入对第几个工作表进行操作：")
    sheetPrompt.pack(side=LEFT)
    sheetMessage.pack(side=LEFT)

    operationCountPrompt = Label(operationCountFrame, text="请选择操作项：")
    operationCountPrompt.pack(side=LEFT)
    operationComboBox.pack(side=LEFT)

    operationColumnPrompt = Label(operationColumnFrame, text="请输入操作列，例如，“3,6”：")
    operationColumnPrompt.pack(side=LEFT)
    operationColumnMessage.pack(side=LEFT)

    myButton.pack(side=LEFT)

    resultPrompt = Label(resultFrame, text="结果：")
    resultPrompt.pack(side=LEFT)
    resultMessage.pack(side=LEFT)

    root.mainloop()
